clear all
capture log close
set more off
set varabbrev off

log using "$logfile/build_list_of_schools.log", replace

clear
import excel "$raw/manual/list_of_schools.xlsx", sheet("Sheet1") firstrow
rename _all, lower
replace schoolname = strlower(schoolname)
replace district = strlower(district)
drop if schoolname==""

replace private=0 if private==.

lab var level910 "having 9 and 10 graders"
lab var level1112 "having 11 and 12 graders"
lab var total910 "total 9 and 10 students"
lab var total1112 "total 11 and 12 students"
lab var tti_ks "TTI reachable: TTI Khuruthang/Samthang"
lab var tti_thimphu "TTI reachable: TTI/IZC Thimphu"
lab var tti_tyangste "TTI reachable: IZC Trashi Yangtse"
lab var stem_cst "STEM reachable: College of Science and Technology"
lab var stem_jnec "STEM reachable:  Jigme Namgyal Engineering College"
lab var stem_gcit "STEM reachable: Gyalpozhing College of InfoTech"
lab var stem_shrubtse "STEM reachable: Sherubtse College"
lab var stem_cnr "STEM reachable: College of Natural Resources"
lab var stem_rtc "STEM reachable: Royal Thimphu College"

lab var dn_tti "distance to nearest TTI/IZC (km)"
lab var dn_stem "distance to nearest STEM college (km)"

lab var trained_counselor "having counselor trained by the program"

save "$clean/list_of_schools.dta", replace

clear
import excel "$raw/manual/list_endline.xlsx", sheet("summary by grade") firstrow

* rename columns 
rename D schoolname
rename F total10
lab var total10 "total 10 students in roster"
rename H total12
lab var total12 "total 12 students in roster"
rename C district

replace district="Wangdue Phodrang" if district=="Wangdu Phodrang"

* drop 
drop if missing(schoolname)
drop if schoolname=="School Name"

keep district schoolname total10 total12 
rename _all, lower
replace schoolname = strlower(schoolname)
replace schoolname="drukgyel central school" if schoolname=="drukgyel  central school"

destring total10 total12, replace

save "$clean/list_of_schools_endline.dta", replace
